/**
 * 
 */
package com.feib.stms.dao.jdbc;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;
import org.springframework.stereotype.Repository;

import com.feib.stms.dao.RoleDao;
import com.feib.stms.dao.jdbc.rowmapper.RoleRowMapper;
import com.feib.stms.dao.jdbc.rowmapper.UserRowMapper;
import com.feib.stms.model.MenuItem;
import com.feib.stms.model.Role;
import com.feib.stms.model.User;

/**
 * @author Jimmy Liu
 *
 */
@Repository("roleDao")
public class RoleJdbcDao extends BaseJdbcDao implements RoleDao
{
	private OracleSequenceMaxValueIncrementer sequence;
	
	private SimpleJdbcInsert userLoginControlJdbcInsert;
	
	@Override
	protected void initDao() throws Exception {
		super.initDao();
		this.sequence = new  OracleSequenceMaxValueIncrementer(this.getDataSource(), "APP_ROLE_PK_SEQ");
		this.userLoginControlJdbcInsert = new  SimpleJdbcInsert(this.getDataSource());
		this.userLoginControlJdbcInsert.withTableName("APP_ROLE")
			.usingColumns("ROLE_PK", "GROUP_PK", "ROLE_NO", "ROLE_NAME", "ROLE_NOTE"
					,"CREATED_DATE", "CREATED_BY", "UPDATED_DATE", "UPDATED_BY");
	}

	public List<Role> getByUserPk(User user) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("SELECT R.*");
		sql.append(" , G.GROUP_NO, G.GROUP_NAME");
		sql.append(" FROM APP_USER_ROLE UR");
		sql.append(" , APP_ROLE R , APP_GROUP G ");
		sql.append(" WHERE UR.ROLE_PK = R.ROLE_PK");
		sql.append(" AND R.GROUP_PK = G.GROUP_PK");
		sql.append(" AND UR.USER_PK = :USER_PK");
		parameters.addValue("USER_PK",  user.getUserPk(), Types.NUMERIC);
		
		return this.getSimpleJdbcTemplate().query(sql.toString(), new RoleRowMapper(), parameters);
	}

	public List<Role> getByMenuItemPk(MenuItem menuItem)	{
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT R.*");
		sql.append(" , G.GROUP_NO, G.GROUP_NAME");
		sql.append(" FROM APP_ROLE R, APP_GROUP G");
		sql.append(" WHERE EXISTS (SELECT 1 FROM");
		sql.append(" APP_ROLE_MENU RM, MENU_ITEM M ");
		sql.append(" WHERE RM.ROLE_PK = R.ROLE_PK");
		sql.append(" AND M.MENU_ITEM_PK = RM.MENU_ITEM_PK");
		sql.append(" AND M.MENU_ITEM_PK = :MENU_ITEM_PK ");
		sql.append(" )");
		sql.append(" AND R.GROUP_PK = G.GROUP_PK ");
		parameters.addValue("MENU_ITEM_PK",  menuItem.getMenuItemPk(), Types.NUMERIC);
		
		return this.getSimpleJdbcTemplate().query(sql.toString(), new RoleRowMapper(), parameters);
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.RoleDao#insert(com.feib.stms.model.Role)
	 */
	public int insert(Role role) {
		int insertCounts = 0;
		try{
			if(role != null){
				role.setCreatedDate(new Date());
				role.setUpdatedBy(role.getCreatedBy());
				role.setUpdatedDate(new Date());
			}
			MapSqlParameterSource parameters = new MapSqlParameterSource();
			Long rolePk = new Long(this.sequence.nextLongValue());
			parameters.addValue("ROLE_PK",  rolePk, Types.NUMERIC);
			parameters.addValue("GROUP_PK",  role.getGroup().getGroupPk(), Types.NUMERIC);
			parameters.addValue("ROLE_NO", role.getRoleNo(), Types.VARCHAR);
			parameters.addValue("ROLE_NAME",  role.getRoleName(), Types.VARCHAR);
			parameters.addValue("ROLE_NOTE",  role.getRoleNote(), Types.VARCHAR);
			parameters.addValue("CREATED_BY",  role.getCreatedBy(), Types.VARCHAR);
			parameters.addValue("CREATED_DATE",  role.getCreatedDate(), Types.TIMESTAMP);
			parameters.addValue("UPDATED_DATE",  role.getUpdatedDate(), Types.TIMESTAMP);
			parameters.addValue("UPDATED_BY",  role.getUpdatedBy(), Types.VARCHAR);
					
			insertCounts = userLoginControlJdbcInsert.execute(parameters);
			
			if (insertCounts > 0)
				role.setRolePk(rolePk);
			return insertCounts;
		}catch (DataAccessException e) {
			logger.error(e.getMessage(), e);
		}
		return 0;
	}

	/** 根據pk查詢
	 * @param roleId
	 * @return Role
	 */
	public Role getByRolePk(String rolePk) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();

		sql.append("select * from app_role where 1=1 ");
		if(null != rolePk && !"".equals(rolePk)){
			sql.append(" And role_pk = :role_pk ");
		}
		parameters.addValue("role_pk",  rolePk, Types.NUMERIC);

		return DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new RoleRowMapper(), parameters));
	}

	/**
	 * 根據PK找到有權限的菜單 PK
	 * @return
	 */
	public List<String> getMenuItemPkByRoleId(String roleId) {
		String sql = "select menu_item_pk from app_role_menu where 1=1 And role_pk = " + roleId + " ";
		List<String> menuItemPk = this.getJdbcTemplate().queryForList(sql, String.class);
		return menuItemPk;
	}

	/**
	 * 查詢所有的系統功能角色
	 */
	public List<Role> getAllSystemRole() {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append("select * from APP_ROLE ");
		return this.getSimpleJdbcTemplate().query(sql.toString(), new RoleRowMapper(), parameters);
	}

	/**
	 * 列出該功能角色下的所有用戶
	 * @param roleNo
	 * @return
	 */
	public List<User> getAllSelectedRoleUser(String rolePk) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append("select distinct ag.* ,au.* from APP_USER_ROLE aur, app_user au, app_group ag where ag.group_pk = au.group_pk and aur.user_pk = au.user_pk ");
		/* 功能角色 */
		if(null != rolePk && !"".equals(rolePk)){
			sql.append(" And aur.role_pk = :role_pk" );
			parameters.put("role_pk", rolePk);
		}
		return this.getSimpleJdbcTemplate().query(sql.toString(), new UserRowMapper(), parameters);
	}

	/**
	 * 列出當前GROUP下的所有用戶
	 * @param qryRoleNo
	 * @param userGroup
	 * @return
	 */
	public List<User> getAllSelectedRoleUserByGroupPk(String rolePk, Long userGroupPk) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append("select au.* from APP_USER_ROLE aur, app_user au where aur.user_pk = au.user_pk ");
		/* 功能角色 */
		if(null != rolePk && !"".equals(rolePk)){
			sql.append(" And aur.role_pk = :role_pk" );
			parameters.put("role_pk", rolePk);
		}
		/* 群組 */
		if(null != userGroupPk && 0L != userGroupPk){
			sql.append(" And au.group_pk = :group_pk" );
			parameters.put("group_pk", userGroupPk);
		}
		return this.getSimpleJdbcTemplate().query(sql.toString(), new UserRowMapper(), parameters);
	}

	/**
	 * 列出當前GROUP下的用戶(除以選擇的用戶外)
	 * @param rolePk
	 * @param userGroupPk
	 * @return
	 */
	public List<User> getAllUnSelectedRoleUserByGroupPk(String rolePk, Long userGroupPk) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append("select appu.* from app_user appu" );
		sql.append(" Where appu.user_pk not in (select au.user_pk from APP_USER_ROLE aur, app_user au where aur.user_pk = au.user_pk ");
		/* 功能角色 */
		if(null != rolePk && !"".equals(rolePk)){
			sql.append(" And aur.role_pk = :role_pk" );
			parameters.put("role_pk", rolePk);
		}
		/* 群組 */
		if(null != userGroupPk && 0L != userGroupPk){
			sql.append(" And au.group_pk = :group_pk" );
			parameters.put("group_pk", userGroupPk);
		}
		sql.append(" ) ");
		if(null != userGroupPk && 0L != userGroupPk){
			sql.append(" And appu.group_pk = :group_pk1");
			parameters.put("group_pk1", userGroupPk);
		}
		return this.getSimpleJdbcTemplate().query(sql.toString(), new UserRowMapper(), parameters);
	}

	/**
	 * 新增角色菜單權限
	 */
	public int insertRoleMenuItemPk(List<String> menuItemPk, Long rolePk) {
		  final Long rolePkFinal = rolePk;
		  final List<String> menuItemPkFinal = menuItemPk;
		  String sql="insert into APP_ROLE_MENU(role_pk, menu_item_pk) values(?,?)";
		  int[] i = this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter()
		  {
		   public void setValues(PreparedStatement ps,int i)throws SQLException
		   {
		    Long menu_item_pk=Long.valueOf(menuItemPkFinal.get(i));
		    ps.setLong(1, rolePkFinal);
		    ps.setLong(2, menu_item_pk);
		   }
		   public int getBatchSize()
		   {
		    return menuItemPkFinal.size();
		   }
		  }); 
		  return i.length;
	}

	/**
	 * 新增角色權限
	 */
	public int insertRoleUser(List<String> userPk, Long rolePk) {
		final Long rolePkFinal = rolePk;
		  final List<String> userPkFinal = userPk;
		  String sql="insert into APP_USER_ROLE(user_pk, role_pk) values(?,?)";
		  int[] i = this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter()
		  {
		   public void setValues(PreparedStatement ps,int i)throws SQLException
		   {
		    Long userPk=Long.valueOf(userPkFinal.get(i));
		    ps.setLong(1, userPk);
		    ps.setLong(2, rolePkFinal);
		   }
		   public int getBatchSize()
		   {
		    return userPkFinal.size();
		   }
		  }); 
		  return i.length;
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.RoleDao#updateRole(com.feib.stms.model.Role)
	 */
	public int updateRole(Role role) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("UPDATE APP_ROLE SET ROLE_NAME = :ROLE_NAME" +
				",ROLE_NOTE = :ROLE_NOTE" +
				",UPDATED_DATE = :UPDATED_DATE" +
				", UPDATED_BY = :UPDATED_BY ");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND ROLE_PK = :ROLE_PK");
		sql.append(" AND GROUP_PK = :GROUP_PK");
		parameters.addValue("ROLE_NAME",  role.getRoleName(), Types.VARCHAR);
		parameters.addValue("ROLE_NOTE",  role.getRoleNote(), Types.VARCHAR);
		parameters.addValue("UPDATED_DATE",  role.getUpdatedDate(), Types.TIMESTAMP);
		parameters.addValue("UPDATED_BY",  role.getUpdatedBy(), Types.VARCHAR);
		parameters.addValue("ROLE_PK", role.getRolePk(), Types.NUMERIC);
		parameters.addValue("GROUP_PK",  role.getGroup().getGroupPk(), Types.NUMERIC);
		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.RoleDao#deleteRoleMenuItemByPk(java.lang.Long)
	 */
	public int deleteRoleMenuItemByPk(Long rolePk) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("DELETE FROM APP_ROLE_MENU");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND ROLE_PK = :ROLE_PK ");
		parameters.addValue("ROLE_PK",  rolePk, Types.NUMERIC);
		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.RoleDao#deleteRoleUserByPk(java.lang.Long)
	 */
	public int deleteRoleUserByPk(Long rolePk) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("DELETE FROM APP_USER_ROLE");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND ROLE_PK = :ROLE_PK ");
		parameters.addValue("ROLE_PK",  rolePk, Types.NUMERIC);
		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.RoleDao#findRolePkByRole(com.feib.stms.model.Role)
	 */
	public Role findRolePkByRole(Role role) {
		if(null != role){
			StringBuffer sql = new StringBuffer();
			Map<String, Object> parameters = new HashMap<String, Object>();
			
			sql.append("SELECT A.*");
			sql.append(" FROM APP_ROLE A WHERE 1=1 ");

			//群組PK
			if(null != role.getGroup()){
				if(null != role.getGroup().getGroupPk() && role.getGroup().getGroupPk() != 0L){
					sql.append(" AND A.GROUP_PK = :GROUP_PK ");
					parameters.put("GROUP_PK", role.getGroup().getGroupPk());
				}
			}
			//角色編號
			if(StringUtils.isNotEmpty(role.getRoleNo())){
				sql.append(" AND A.ROLE_NO = :ROLE_NO ");
				parameters.put("ROLE_NO", role.getRoleNo());
			}
			//角色名稱
//			if(StringUtils.isNotEmpty(role.getRoleName())){
//				sql.append(" AND A.ROLE_NAME = :ROLE_NAME ");
//				parameters.put("ROLE_NAME", role.getRoleName());
//			}
			//角色備註
//			if(StringUtils.isNotEmpty(role.getRoleNote())){
//				sql.append(" AND A.ROLE_NOTE = :ROLE_NOTE ");
//				parameters.put("ROLE_NOTE", role.getRoleNote());
//			}else{
//				sql.append(" AND A.ROLE_NOTE IS NULL ");
//			}
			return this.getSimpleJdbcTemplate().queryForObject(sql.toString(), new RoleRowMapper(), parameters);
		}else{
			return null;
		}
	}

	public List<Role> findByGroupPk(String groupPk) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append("select * from APP_ROLE where 1=1 ");
		if(StringUtils.isNotEmpty(groupPk)){
			sql.append(" And group_pk = :group_pk ");
			parameters.put("group_pk", groupPk);
		}
		return this.getSimpleJdbcTemplate().query(sql.toString(), new RoleRowMapper(), parameters);
	}

}
